package cs340.stormtroopers.database;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import cs340.stormtroopers.hypeerweb.CompleteNodeDomain;
import cs340.stormtroopers.hypeerweb.Node;

// by Andrew Morrow

public class HyPeerWebDatabase
{
	public static String DATABASE_DIRECTORY = System.getProperty("user.home")
			+ "/CS340HyPeerWebData/";
	public static String DEFAULT_DATABASE_NAME = "data.sqlite";
	private static HyPeerWebDatabase singleton = null;

	private Connection connection;

	private HyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		doInitHyPeerWebDatabase(dbName);
	}

	// returns null if you have not called initHyPeerWebDatabase() at least once
	public static HyPeerWebDatabase getSingleton() throws IOException,
			ClassNotFoundException, SQLException
	{
		if (singleton == null)
			singleton = new HyPeerWebDatabase(DEFAULT_DATABASE_NAME);
		return singleton;
	}

	public static void initHyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		getSingleton().doInitHyPeerWebDatabase(dbName);
	}

	private void doInitHyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		if (connection != null)
			connection.close();
		// check that the database file exists
		// create it and its parent directories if they don't
		File databaseFile = new File(DATABASE_DIRECTORY + dbName);
		if (!databaseFile.exists())
		{
			databaseFile.getParentFile().mkdirs();
			databaseFile.createNewFile();
		}

		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection("jdbc:sqlite:"
				+ databaseFile.getAbsolutePath());
		createTablesIfNeeded();
	}

	public static void initHyPeerWebDatabase() throws IOException,
			ClassNotFoundException, SQLException
	{
		initHyPeerWebDatabase(DEFAULT_DATABASE_NAME);
	}

	public void clear() throws SQLException
	{
		dropTables();
		createTablesIfNeeded();
	}

	public void close() throws SQLException
	{
		connection.close();
	}
	
	///////////////////////////////////////////////////////////////// SAVING NODES
	
	public void saveNodes(Collection<Node> nodes) throws SQLException, IOException, ClassNotFoundException
	{
		saveNodeDelegates(NormalNodeDelegate.wrap(nodes));
	}
	
	public void saveNodeDelegates(Iterator<Delegate> nodeIterator) throws SQLException, IOException, ClassNotFoundException
	{
		while (nodeIterator.hasNext())
		{
			Delegate d = nodeIterator.next();
			d.insertIntoDatabase(connection);
		}
	}

	///////////////////////////////////////////////////////////////// GETTING NODES
	
	public ArrayList<CompleteNodeDomain> getCompleteNodeDomainSet()
			throws SQLException
	{
		ArrayList<CompleteNodeDomain> domains = new ArrayList<CompleteNodeDomain>();
		ResultSet nodes = connection.createStatement().executeQuery(
				"SELECT * FROM Node");
		while (nodes.next())
			domains.add(getNodeFromResultSet(nodes));
		nodes.close();
		return domains;
	}

	private CompleteNodeDomain getNodeFromResultSet(ResultSet resultSet)
			throws SQLException
	{
		CompleteNodeDomain node = new CompleteNodeDomain();
		int webId = resultSet.getInt("WebId");
		node.setWebId(webId);
		node.setHeight(resultSet.getInt("Height"));
		node.setFold(resultSet.getInt("FoldId"));
		node.setSurrogateFold(resultSet.getInt("SurrogateFoldId"));
		node.setInverseSurrogateFold(resultSet.getInt("InverseSurrogateFoldId"));

		node.setNeighborsBiggerThanMe(getNeighborsBiggerThanMe(webId));
		node.setParentNeighborsSmallerThanMe(getParentNeighborsSmallerThanMe(webId));
		node.setChildlessNeighborsSmallerThanMe(getChildlessNeighborsSmallerThanMe(webId));

		node.setDownPointers(getSurrogateNeighbors(webId));
		node.setUpPointers(getInverseSurrogateNeighbors(webId));

		return node;
	}

	private ArrayList<Integer> getNeighborsBiggerThanMe(int webId)
			throws SQLException
	{
		PreparedStatement getNeighbors = connection
				.prepareStatement("SELECT Right FROM NeighborsBiggerThanMe WHERE Left = ? ORDER BY Right");
		getNeighbors.setInt(1, webId);
		ResultSet neighbors = getNeighbors.executeQuery();
		ArrayList<Integer> neighborIds = new ArrayList<Integer>();

		while (neighbors.next())
			neighborIds.add(neighbors.getInt("Right"));

		getNeighbors.close();
		return neighborIds;
	}

	private ArrayList<Integer> getChildlessNeighborsSmallerThanMe(int webId)
			throws SQLException
	{
		PreparedStatement getNeighbors = connection
				.prepareStatement("SELECT Right FROM ChildlessNeighborsSmallerThanMe WHERE Left = ? ORDER BY Right");
		getNeighbors.setInt(1, webId);
		ResultSet neighbors = getNeighbors.executeQuery();
		ArrayList<Integer> neighborIds = new ArrayList<Integer>();

		while (neighbors.next())
			neighborIds.add(neighbors.getInt("Right"));

		getNeighbors.close();
		return neighborIds;
	}

	private ArrayList<Integer> getParentNeighborsSmallerThanMe(int webId)
			throws SQLException
	{
		PreparedStatement getNeighbors = connection
				.prepareStatement("SELECT Right FROM ParentNeighborsSmallerThanMe WHERE Left = ? ORDER BY Right");
		getNeighbors.setInt(1, webId);
		ResultSet neighbors = getNeighbors.executeQuery();
		ArrayList<Integer> neighborIds = new ArrayList<Integer>();

		while (neighbors.next())
			neighborIds.add(neighbors.getInt("Right"));

		getNeighbors.close();
		return neighborIds;
	}

	private ArrayList<Integer> getSurrogateNeighbors(int webId)
			throws SQLException
	{
		PreparedStatement getSurrogateNeighbors = connection
				.prepareStatement("SELECT Bottom FROM SurrogateNeighbor WHERE Top = ?");
		getSurrogateNeighbors.setInt(1, webId);
		ResultSet surrogateNeighbors = getSurrogateNeighbors.executeQuery();
		ArrayList<Integer> surrogateNeighborIds = new ArrayList<Integer>();

		while (surrogateNeighbors.next())
			surrogateNeighborIds.add(surrogateNeighbors.getInt("Bottom"));

		getSurrogateNeighbors.close();
		return surrogateNeighborIds;
	}

	private ArrayList<Integer> getInverseSurrogateNeighbors(int webId)
			throws SQLException
	{
		PreparedStatement getInverseSurrogateNeighbors = connection
				.prepareStatement("SELECT Top FROM SurrogateNeighbor WHERE Bottom = ?");
		getInverseSurrogateNeighbors.setInt(1, webId);
		ResultSet inverseSurrogateNeighbors = getInverseSurrogateNeighbors
				.executeQuery();
		ArrayList<Integer> inverseSurrogateNeighborIds = new ArrayList<Integer>();

		while (inverseSurrogateNeighbors.next())
			inverseSurrogateNeighborIds.add(inverseSurrogateNeighbors
					.getInt("Top"));

		getInverseSurrogateNeighbors.close();
		return inverseSurrogateNeighborIds;
	}

	///////////////////////////////////////////////////////////////// CREATING TABLES
	
	private void createTablesIfNeeded() throws SQLException
	{
		Statement createTablesStatement = connection.createStatement();
		createTablesStatement.addBatch("CREATE TABLE IF NOT EXISTS Node ("
				+ "WebId int primary key not null unique,"
				+ "Height int not null," + "FoldId int,"
				+ "SurrogateFoldId int," + "InverseSurrogateFoldId int)");
		createTablesStatement
				.addBatch("CREATE TABLE IF NOT EXISTS SurrogateNeighbor ("
						+ "Id int primary key asc," + "Top int not null,"
						+ "Bottom int not null)");
		createTablesStatement
				.addBatch("CREATE TABLE IF NOT EXISTS NeighborsBiggerThanMe ("
						+ "Id int primary key asc," + "Left int not null,"
						+ "Right int not null)");
		createTablesStatement
				.addBatch("CREATE TABLE IF NOT EXISTS ChildlessNeighborsSmallerThanMe ("
						+ "Id int primary key asc,"
						+ "Left int not null,"
						+ "Right int not null)");
		createTablesStatement
				.addBatch("CREATE TABLE IF NOT EXISTS ParentNeighborsSmallerThanMe ("
						+ "Id int primary key asc,"
						+ "Left int not null,"
						+ "Right int not null)");
		createTablesStatement.executeBatch();
		createTablesStatement.close();

		Statement createIndexStatement = connection.createStatement();
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS SurrogateNeighborTopIndex ON SurrogateNeighbor(Top)");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS SurrogateNeighborBottomIndex ON SurrogateNeighbor(Bottom)");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS NeighborsBiggerThanMeIndex ON NeighborsBiggerThanMe(Left)");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS ChildlessNeighborsSmallerThanMeIndex ON ChildlessNeighborsSmallerThanMe(Left)");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS ParentNeighborsSmallerThanMeIndex ON  ParentNeighborsSmallerThanMe(Left)");

		createIndexStatement.executeBatch();
		createIndexStatement.close();
	}
	
	///////////////////////////////////////////////////////////////// DROPPING TABLES

	private void dropTables() throws SQLException
	{
		Statement dropIndexesStatement = connection.createStatement();
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS SurrogateNeighborTopIndex");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS SurrogateNeighborBottomIndex");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS NeighborsBiggerThanMeIndex");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS ChildlessNeighborsSmallerThanMeIndex");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS ParentNeighborSmallerThanMeIndex");
		dropIndexesStatement.executeBatch();
		dropIndexesStatement.close();

		Statement dropTablesStatement = connection.createStatement();
		dropTablesStatement.addBatch("DROP TABLE IF EXISTS Node");
		dropTablesStatement.addBatch("DROP TABLE IF EXISTS SurrogateNeighbor");
		dropTablesStatement
				.addBatch("DROP TABLE IF EXISTS NeighborsBiggerThanMe");
		dropTablesStatement
				.addBatch("DROP TABLE IF EXISTS ChildlessNeighborsSmallerThanMe");
		dropTablesStatement
				.addBatch("DROP TABLE IF EXISTS ParentNeighborsSmallerThanMe");
		dropTablesStatement.executeBatch();
		dropTablesStatement.close();
	}
}
